﻿Public Class DocAlarmCheckViewForm

    Private Sub DocAlarmCheckViewForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.DataGridView1.AutoGenerateColumns = False
    End Sub


    Private Sub btnRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRefresh.Click
        If CheckValidate() = False Then
            Exit Sub
        End If

        QueryData()
    End Sub

    Private Sub QueryData()

        Dim strSql As String
        strSql = "Select * From tbccCheckAlarm where checkTime >=?p0 and checkTime<=?p1 and projectID= ?p2"
        Dim p(2) As MySql.Data.MySqlClient.MySqlParameter
        For i As Integer = 0 To p.Length - 1
            p(i) = New MySql.Data.MySqlClient.MySqlParameter()
        Next

        p(0).ParameterName = "p0" : p(0).Value = UtilityClass.GetDateString(Me.dtpFrom.Value)
        p(1).ParameterName = "p1" : p(1).Value = UtilityClass.GetDateString(Me.dtpTo.Value)
        p(2).ParameterName = "p2" : p(2).Value = MainForm.pi.ProjectID

        Dim ds As DataSet
        ds = SqlHelper.ExecuteDataset(strSql, p)

        For Each r As DataRow In ds.Tables(0).Rows
            If r("resultLight") = 1 Then
                r("resultLight") = "正常"
            Else
                r("resultLight") = "异常"
            End If

            If r("resultPhone") = 1 Then
                r("resultPhone") = "正常"
            Else
                r("resultPhone") = "异常"
            End If

        Next

        Me.DataGridView1.DataSource = ds.Tables(0)


        For i As Integer = 0 To Me.DataGridView1.Rows.Count - 1
            For j As Integer = 1 To Me.DataGridView1.Columns.Count - 1
                If Convert.ToString(Me.DataGridView1.Rows(i).Cells(j).Value) = "异常" Then
                    Me.DataGridView1.Rows(i).Cells(j).Style.ForeColor = Color.Red
                End If
            Next
        Next



        If ds.Tables(0).Rows.Count = 0 Then
            Me.btnPrint.Enabled = False
            msg.Show("指定的时间范围内没有自检记录")
        Else
            Me.btnPrint.Enabled = True
        End If
    End Sub

    ''' <summary>
    ''' 检查时间范围是否合法
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function CheckValidate() As Boolean
        If Me.dtpTo.Value < Me.dtpFrom.Value Then
            msg.ShowWarning("起始时间不能大于终止时间。")
            Me.dtpFrom.Focus()
            Return False
        End If

        Return True
    End Function

    Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click
        Me.Cursor = Cursors.WaitCursor
        sender.Enabled = False
        Print()
        sender.Enabled = True
        Me.Cursor = Cursors.Default
    End Sub


    Private Sub Print()
        'has data
        Dim dt As DataGridView
        dt = Me.DataGridView1
        If dt Is Nothing OrElse dt.Rows.Count = 0 Then
            msg.Show("没有数据")
            Exit Sub
        End If

        'Excel object
        Dim objExcel As New Excel.Application
        Dim wb As Excel.Workbook = objExcel.Workbooks.Add()

        Dim headers(100) As String

        'Get head text
        Dim index As Integer = 0
        headers(index) = "序号"
        For i As Integer = 0 To Me.DataGridView1.Columns.Count - 1
            If Me.DataGridView1.Columns(i).Visible Then
                index += 1
                headers(index) = Me.DataGridView1.Columns(i).HeaderText
            End If
        Next

        Dim values(dt.Rows.Count, dt.Columns.Count) As Object

        'Set headers
        For j As Integer = 0 To dt.Columns.Count '序号
            values(0, j) = headers(j)
        Next

        'Set values
        For i As Integer = 0 To dt.Rows.Count - 1
            Dim r As DataGridViewRow = dt.Rows(i)
            values(i + 1, 0) = i + 1
            For j As Integer = 0 To dt.Columns.Count - 1
                values(i + 1, j + 1) = r.Cells(j).Value 'j+1序号
                If values(i + 1, j + 1).ToString = "异常" Then
                    'MessageBox.Show("异常")
                    objExcel.Cells(i + 2, j + 2).Font.Color = RGB(255, 0, 0) '设置"异常"颜色
                End If
            Next
        Next

        'Print
        Dim pwd As String = System.Guid.NewGuid().ToString()
        Try
            objExcel.Range(objExcel.Cells(1, 1), objExcel.Cells(dt.Rows.Count + 1, dt.Columns.Count + 1)).Value = values 'col+1序号

            'Set format
            objExcel.Columns("A:AA").Select()
            objExcel.Selection.HorizontalAlignment = Excel.Constants.xlCenter
            objExcel.Selection.Font.Size = 10
            objExcel.Selection.ColumnWidth = 10

            Dim selectRange As Excel.Range
            '序号
            selectRange = objExcel.Range("A:A")
            selectRange.ColumnWidth = 5

            '时间
            selectRange = objExcel.Range("B:B")
            selectRange.ColumnWidth = 20
            selectRange.NumberFormatLocal = "yyyy-mm-dd hh:mm:ss"

            '操作用户
            selectRange = objExcel.Range("C:C")
            selectRange.ColumnWidth = 15

            '报警
            selectRange = objExcel.Range("D:E")
            selectRange.ColumnWidth = 10

            '电话号码
            selectRange = objExcel.Range("F:F")
            selectRange.ColumnWidth = 20

            '设置边框
            Dim range As Excel.Range = objExcel.Range(objExcel.Cells(1, 1), objExcel.Cells(dt.Rows.Count + 1, dt.Columns.Count + 1))
            range.Borders.Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous
            range.Borders.Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous
            range.Borders.Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous
            range.Borders.Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous
            range.Borders.Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlContinuous
            range.Borders.Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlContinuous

            '设置保护
            Try
                objExcel.ActiveSheet.Protect(pwd, _
                            AllowFormattingCells:=False, _
                            AllowFormattingColumns:=False, _
                            AllowFormattingRows:=False, _
                            AllowInsertingColumns:=False, _
                            AllowInsertingRows:=False, _
                            AllowInsertingHyperlinks:=False, _
                            AllowDeletingColumns:=False, _
                            AllowDeletingRows:=False _
                )
            Catch ex As Exception
                objExcel.ActiveSheet.Protect(pwd)
            End Try

            'Set page
            Dim ws As Excel.Worksheet = objExcel.ActiveSheet
            With ws.PageSetup
                .LeftHeader = "&G" & vbCr & String.Format(" 监控时间：{0}至{1}", Me.dtpFrom.Value, Me.dtpTo.Value) & vbCr & vbCr
                .CenterHeader = String.Format("&""宋体,加粗""&28 报警系统自检报告 ")
                .RightHeader = vbCr & vbCr & vbCr & "共&N页 第&P页"
                .LeftFooter = "养护员："
                .CenterFooter = ""
                .RightFooter = "打印日期：&D &T"
                .LeftMargin = objExcel.Application.InchesToPoints(0.6)
                .RightMargin = objExcel.Application.InchesToPoints(0.4)
                .TopMargin = objExcel.Application.InchesToPoints(1.08)
                .BottomMargin = objExcel.Application.InchesToPoints(0.68)
                .HeaderMargin = objExcel.Application.InchesToPoints(0.22)
                .FooterMargin = objExcel.Application.InchesToPoints(0.5)
                .PrintHeadings = False
                .PrintGridlines = True
                '.PrintComments = objExcel.xlPrintNoComments
                ''.PrintQuality = 300
                .CenterHorizontally = False
                .CenterVertically = False
                '.Orientation = objExcel.xlPortrait
                .Draft = False
                '.PaperSize = Excel.XlPaperSize.xlPaperA4
                .Orientation = Excel.XlPageOrientation.xlPortrait '纵向打印
                '.FirstPageNumber = objExcel.xlAutomatic
                '.Order = objExcel.xlDownThenOver
                '.BlackAndWhite = False
                .Zoom = 100
                '.PrintErrors = objExcel.xlPrintErrorsDisplayed

                '.PrintArea = "A1:U" & bRow
                .FitToPagesWide = 1
                .FitToPagesTall = 1
                .PrintTitleRows = "$1:$1"

                Try
                    With .LeftHeaderPicture
                        .Filename = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) & "\ClientLogo.jpg"
                        .Height = 68
                        .Width = 68
                        .LockAspectRatio = Microsoft.Office.Core.MsoTriState.msoFalse
                    End With
                Catch ex As Exception
                    .LeftHeader = vbCr & vbCr & vbCr & String.Format(" 监控时间：{0}至{1}", Me.dtpFrom.Value, Me.dtpTo.Value) & vbCr & vbCr
                    Debug.WriteLine("页眉图片" & ex.ToString())
                End Try
                
            End With

            'Show Print
            objExcel.Visible = True
            objExcel.ActiveWindow.SelectedSheets.PrintPreview()

        Catch ex As Exception
            Debug.WriteLine("打印错误" & ex.ToString())
            msg.Show(ex.Message)
        Finally
            Try
                If objExcel.ActiveWindow.View = Excel.XlWindowView.xlNormalView Then '分页预览
                    'objExcel.ActiveSheet.Unprotect(pwd)
                    wb.Close(False, False)
                    objExcel.DisplayAlerts = False
                    objExcel.Quit()
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(wb)
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel)
                    wb = Nothing
                    objExcel = Nothing
                    GC.Collect()
                End If
            Catch ex As Exception
                Debug.WriteLine(ex.ToString())
            End Try

        End Try
    End Sub
End Class